package io.framework.modules.noise.utils;

import io.framework.common.utils.DateUtils;
import io.framework.modules.air.utils.AirFileCommonUtil;
import io.framework.modules.noise.dto.NoiseFunctionalZoneDTO;
import io.framework.modules.noise.dto.NoiseMonitorDTO;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * /**
 * 〈噪声数据上传〉
 *
 * @Author GJQ
 * @Create 2020/6/1
 * @Since 1.0.0
 */

public class NoiseFileCommonUtil {

    public static void main(String[] args) {
        /*String filePath = "E:\\workspace\\projects_rongmo\\资料\\三亚环保项目\\Aaa第二版-新数据\\环保局数据\\2018-2020年空气质量监测数据、噪声数据汇总-技管室2020-05-29\\2018-2019区域、交通噪声\\";
        String fileName = "2018年三亚市区域噪声统计表－存档表.xls";
        filePath = filePath + fileName;
        List<NoiseMonitorDTO> noiseMonitorDTOList = new ArrayList<>();
        List<NoiseMonitorDTO> zhoujianList = NoiseFileCommonUtil.readNoiseData(1, 2, "2018", filePath);
        if (CollectionUtils.isNotEmpty(zhoujianList)) {
            noiseMonitorDTOList.addAll(zhoujianList);
        }
        List<NoiseMonitorDTO> yejianList = readNoiseData(2, 2, "2018", filePath);
        if (CollectionUtils.isNotEmpty(yejianList)) {
            noiseMonitorDTOList.addAll(yejianList);
        }
        System.out.println(noiseMonitorDTOList.toString());*/

        //功能区测试
        String filePath = "E:\\workspace\\projects_rongmo\\资料\\三亚环保项目\\Aaa第二版-新数据\\环保局数据\\2018-2020年空气质量监测数据、噪声数据汇总-技管室2020-05-29\\";
        String fileName = "2018-2020年功能区噪声昼、夜等级声级汇总与评价.xls";
        filePath = filePath + fileName;
        List<NoiseFunctionalZoneDTO> list = readNoiseZoneData(filePath);
        list.forEach(v -> {
            System.out.println(v.toString());
        });
    }

    /**
     * 交通噪声/区域噪声 数据处理
     *
     * @param sheetNum
     * @param classify 1-交通噪声 2-区域噪声
     * @param year
     * @param url
     * @return
     */
    public static List<NoiseMonitorDTO> readNoiseData(int sheetNum, int classify, String year, String url) {
        Workbook workbook = AirFileCommonUtil.readExcel(url);
        List<NoiseMonitorDTO> noiseMonitorDTOList = null;
        if (workbook != null) {
            noiseMonitorDTOList = new ArrayList<>();
            Sheet sheet = null;
            // 1-昼间 2-夜间
            int type = 0;
            try {
                sheet = workbook.getSheetAt(sheetNum);
                //sheet名称，用于校验模板是否正确
                String sheetName = sheet.getSheetName();
                if (sheetName.contains("昼")) {
                    type = 1;
                } else if (sheetName.contains("夜")) {
                    type = 2;
                }
            } catch (IllegalArgumentException ie) {
                return null;
            }
            int rown = sheet.getPhysicalNumberOfRows();
            Row row = null;
            for (int i = 1; i < rown; i++) {
                if (classify == 1) {
                    if (i < 4) {
                        continue;
                    }
                } else {
                    if (i < 3) {
                        continue;
                    }
                }
                row = sheet.getRow(i);
                if (row != null) {
                    if (StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(0)) + "") &&
                            StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(1)) + "") &&
                            StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(2)) + "")) {

                        /**
                         * 赋值
                         * type  1-昼间 2-夜间
                         * classify  1-交通噪声 2-区域噪声
                         */
                        NoiseMonitorDTO noiseMonitorDTO = setNoiseValue(classify, year, row, type);

                        if (StringUtils.isNotBlank(noiseMonitorDTO.getStationName())) {
                            noiseMonitorDTO.setMonth(DateUtils.parse(year, DateUtils.DATE_YEAR_PATTERN));
                            noiseMonitorDTOList.add(noiseMonitorDTO);
                        }
                    }
                }
            }
        }
        return noiseMonitorDTOList;
    }

    /**
     * 功能区噪声 数据处理
     *
     * @param url
     * @return
     */
    public static List<NoiseFunctionalZoneDTO> readNoiseZoneData(String url) {
        Workbook workbook = AirFileCommonUtil.readExcel(url);
        List<NoiseFunctionalZoneDTO> list = new ArrayList<>();
        if (workbook != null) {
            Iterator<Sheet> sheets = null;
            try {
                sheets = workbook.iterator();
            } catch (IllegalArgumentException ie) {
                return null;
            }
            while (sheets.hasNext()) {
                Sheet sheet = sheets.next();
                int rown = sheet.getPhysicalNumberOfRows();
                Row row = null;
                for (int i = 1; i < rown; i++) {
                    if (i < 4) {
                        continue;
                    }
                    row = sheet.getRow(i);
                    if (row != null) {
                        if (StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(0)) + "") &&
                                StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(1)) + "") &&
                                StringUtils.isNotBlank(AirFileCommonUtil.getCellFormatValue(row.getCell(2)) + "")) {

                            String year = sheet.getSheetName();

                            //赋值
                            List<NoiseFunctionalZoneDTO> noisrList = setNoiseFunctionalZoneDTO(row);
                            noisrList.forEach(v -> {
                                if (StringUtils.isNotBlank(v.getStationName())) {
                                    v.setMonth(DateUtils.parse(year, DateUtils.DATE_YEAR_PATTERN));
                                    list.add(v);
                                }
                            });
                        }
                    }
                }
            }
        }
        return list;
    }

    public static List<String> getYear(String url) {
        Workbook workbook = AirFileCommonUtil.readExcel(url);
        List<String> yearList = new ArrayList<>();
        if (workbook != null) {
            Iterator<Sheet> sheets = null;
            try {
                sheets = workbook.iterator();
            } catch (IllegalArgumentException ie) {
                return null;
            }
            while (sheets.hasNext()) {
                Sheet sheet = sheets.next();
                yearList.add(sheet.getSheetName());
            }
        }
        return yearList;
    }

    /**
     * 给 交通噪声/区域噪声 赋值
     *
     * @param classify
     * @param year
     * @param row
     * @return
     */
    public static NoiseMonitorDTO setNoiseValue(int classify, String year, Row row, int type) {
        NoiseMonitorDTO noiseMonitorDTO = new NoiseMonitorDTO();
        int i = 0;
        Object obj = null;
        noiseMonitorDTO.setCode(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)) + "");
        Object stationName = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
        noiseMonitorDTO.setStationName(stationName == null ? "" : stationName.toString());
        Object month = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
        Object day = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
        Object hour = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
        Object minute = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
        Date time = null;
        if (month != null && day != null && hour != null && minute != null) {
            String date = year + "-" + getDateInteger(month) + "-" + getDateInteger(day) + " " + getDateInteger(hour) + ":" + getDateInteger(minute) + ":00";
            time = DateUtils.parse(date, DateUtils.DATE_TIME_PATTERN);
        }
        if (StringUtils.isNotBlank(noiseMonitorDTO.getStationName())) {
            noiseMonitorDTO.setDate(time);
            System.out.println(noiseMonitorDTO.getStationName());
            BigDecimal leq = (BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
            noiseMonitorDTO.setLeq(leq);
            noiseMonitorDTO.setL10((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            noiseMonitorDTO.setL50((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            noiseMonitorDTO.setL90((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            noiseMonitorDTO.setLmax((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            noiseMonitorDTO.setLmin((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            noiseMonitorDTO.setSd((BigDecimal) AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
            //1-交通噪声 2-区域噪声 3-功能区噪声
            if (classify == 1) {
                obj = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
                noiseMonitorDTO.setCarLarge(obj == null ? "" : obj.toString());
                obj = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
                noiseMonitorDTO.setCarSmall(obj == null ? "" : obj.toString());
            } else if (classify == 2) {
                obj = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
                noiseMonitorDTO.setNoiseCode(obj == null ? "" : getDateInteger(obj).toString());
                obj = AirFileCommonUtil.getCellFormatValue(row.getCell(i++));
                noiseMonitorDTO.setZoneCode(obj == null ? "" : getDateInteger(obj).toString());
            }
            Object o = AirFileCommonUtil.getCellFormatValue(row.getCell(i));
            noiseMonitorDTO.setRemark(o == null ? "" : o.toString());
            noiseMonitorDTO.setClassify(classify);
            noiseMonitorDTO.setType(type);

            if (classify == 1) {//1-交通噪声
                //type 1-昼间 2-夜间
                //compareTo   -1 小于 0 等于 1 大于
                noiseMonitorDTO.setPass(type == 1 ? (leq.compareTo(new BigDecimal(70)) < 0 ? 1 : 2) : (leq.compareTo(new BigDecimal(55)) < 0 ? 1 : 2));
            } else {//2-区域噪声
                //type 1-昼间 2-夜间
                //compareTo   -1 小于 0 等于 1 大于
                if ("1".equals(noiseMonitorDTO.getZoneCode())) {
                    noiseMonitorDTO.setPass(type == 1 ? (leq.compareTo(new BigDecimal(55)) < 0 ? 1 : 2) : (leq.compareTo(new BigDecimal(45)) < 0 ? 1 : 2));
                } else if ("2".equals(noiseMonitorDTO.getZoneCode())) {
                    noiseMonitorDTO.setPass(type == 1 ? (leq.compareTo(new BigDecimal(60)) < 0 ? 1 : 2) : (leq.compareTo(new BigDecimal(50)) < 0 ? 1 : 2));
                }
            }
        }
        return noiseMonitorDTO;
    }

    /**
     * 给 功能区噪音赋值
     *
     * @param row
     * @return
     */
    public static List<NoiseFunctionalZoneDTO> setNoiseFunctionalZoneDTO(Row row) {
        List<NoiseFunctionalZoneDTO> list = new ArrayList<>();
        NoiseFunctionalZoneDTO dto = new NoiseFunctionalZoneDTO();
        int i = 0;
        dto.setClassify(getDateInteger(AirFileCommonUtil.getCellFormatValue(row.getCell(i++))));
        dto.setStationName(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)) + "");
        BigDecimal Ld1 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ln1 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ld2 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ln2 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ld3 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ln3 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ld4 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i++)));
        BigDecimal Ln4 = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(i)));
        //昼间评价标准
        BigDecimal LdStandard = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(11)));
        //夜间评价标准
        BigDecimal LnStandard = getDateBigDecimal(AirFileCommonUtil.getCellFormatValue(row.getCell(12)));
        Object o = AirFileCommonUtil.getCellFormatValue(row.getCell(20));
        dto.setRemark(o == null ? "" : o.toString());
        if (Ld1 != null && Ln1 != null) {
            NoiseFunctionalZoneDTO target = new NoiseFunctionalZoneDTO();
            BeanUtils.copyProperties(dto, target);
            fillData(target, Ld1, Ln1, LdStandard, LnStandard, 1);
            list.add(target);
        }
        if (Ld2 != null && Ln2 != null) {
            NoiseFunctionalZoneDTO target = new NoiseFunctionalZoneDTO();
            BeanUtils.copyProperties(dto, target);
            fillData(target, Ld2, Ln2, LdStandard, LnStandard, 2);
            list.add(target);
        }
        if (Ld3 != null && Ln3 != null) {
            NoiseFunctionalZoneDTO target = new NoiseFunctionalZoneDTO();
            BeanUtils.copyProperties(dto, target);
            fillData(target, Ld3, Ln3, LdStandard, LnStandard, 3);
            list.add(target);
        }
        if (Ld4 != null && Ln4 != null) {
            NoiseFunctionalZoneDTO target = new NoiseFunctionalZoneDTO();
            BeanUtils.copyProperties(dto, target);
            fillData(target, Ld4, Ln4, LdStandard, LnStandard, 4);
            list.add(target);
        }
        return list;
    }

    public static void fillData(NoiseFunctionalZoneDTO dto, BigDecimal Ld, BigDecimal Ln, BigDecimal LdStandard, BigDecimal LnStandard, Integer quarter) {
        dto.setLd(Ld);
        dto.setLn(Ln);
        dto.setLdStandard(LdStandard);
        dto.setLnStandard(LnStandard);
        Boolean LdPass = Ld.compareTo(LdStandard) > 0;
        Boolean LnPass = Ln.compareTo(LnStandard) > 0;
        dto.setLdPass(LdPass ? "超标" : "达标");
        dto.setLnPass(LnPass ? "超标" : "达标");
        dto.setQuarter(quarter);
    }

    public static Integer getDateInteger(Object obj) {
        Integer result = 0;
        if (obj != null) {
            if (obj instanceof BigDecimal) {
                result = ((BigDecimal) obj).intValue();
            } else if (obj instanceof String) {
                result = Integer.parseInt(obj.toString().replaceAll(" ", ""));
            }
        }
        return result;
    }

    public static BigDecimal getDateBigDecimal(Object obj) {
        BigDecimal result = null;
        if (obj != null) {
            if (obj instanceof String) {
                if (((String) obj).contains("无效")) {
                    return result;
                }
            } else if (obj instanceof BigDecimal) {
                result = (BigDecimal) obj;
            }
        }
        return result;
    }
}
